using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Configuration;

namespace Scheduler
{
    /*
     * An instance of this class can be used to open and close a 
     * connectionn to the database.
     * Once the connection is established, the read, write, and 
     * remove methods can be called to query and store and update 
     * information in each table of the database.
     */ 
    public class Database
    {

        private SqlConnection DBConnection;

        public Database()
        {
            try
            {
                DBConnection = new SqlConnection("Server=.\\SQLEXPRESS;Database=WebDb;Trusted_Connection=True;");
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception in Database constructor:" + e.Message);
            }
        }


        /*
         * Open a connection to the local database
         * by creating a connection object and then attempting to
         * connect to the DB
         * return 1 if error
         * return 0 if no errors
         */
        public int OpenDB()
        {
            try
            {
                DBConnection.Open();
            }
            catch(Exception e)
            {
                Console.WriteLine(e.Message);
                return 1;
            }

            return 0;
        }

        /*
         * Close the connection to the database
         * The database cannot be used afterwards untill another connection
         * is made, either with this Database object or a new one.
         * return 1 if error
         * return 0 otherwise
         */
        public int CloseDB()
        {
            try
            {
                DBConnection.Close();
            }
            catch(Exception e)
            {
                Console.WriteLine(e.Message);
                return 1;
            }

            return 0;
        }

        /*
         * This methods reads information from the TherapyPrice table of the
         * database. These information are selected based on the rmtID and therID
         * parameters that are passed to this method. 
         * After getting all the information, this method builds objects of type 
         * TherapyPrice and places them in a LinkedList and returns that LinkedList.
         */ 
        public LinkedList <TherapyPrice> readTherapyPriceList(String rmtID, String therID) 
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            LinkedList<TherapyPrice> therapyPriceList = new LinkedList<TherapyPrice>();
            try
            {
                commandText = "SELECT RMTID, TherapyID, Duration, Price " +
                               "FROM TherapyPrice " +
                                "WHERE RMTID = '" + rmtID + "' AND TherapyID = '" +
                                therID + "';";
                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();
                while (readResults.Read())
                {
                    TherapyPrice curTherPrice = new TherapyPrice(int.Parse(readResults["Duration"].ToString()),
                                                                int.Parse(readResults["Price"].ToString()));
                    therapyPriceList.AddLast(curTherPrice);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excep in read:" + e.ToString());
                return null;
            }
            finally 
            {
                if (readResults != null)
                    readResults.Close();
            }
          return therapyPriceList;
        }

        /*
         * This method writes the information about TherapyPrice curTP in the 
         * relevant spot on the TherapyPrice table of the database. The spot is 
         * chosen based on rmtID and therID which are passed as parameters to this 
         * funstion. 
         * If the spot in the database already is written, it will just update it 
         * with the new value of the curTP.
         */ 
        public void writeTherapyPrice(TherapyPrice curTP, String rmtID, String therID)
        {
            String commandText = "";
            try
            {//Try to write
                commandText = "INSERT INTO dbo.TherapyPrice VALUES('" +
                                  rmtID + "','" + therID + "'," + curTP.Duration +
                                  "," + curTP.Price.ToString() + ");";
                SqlCommand writeTP = new SqlCommand(commandText, DBConnection);
                writeTP.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                try
                {//Try to update if write unsuccessful.
                    commandText = "UPDATE dbo.TherapyPrice SET Price = " + curTP.Price.ToString() +
                                     "WHERE RMTID = '" + rmtID + "' AND TherapyID = '" + therID + 
                                     "' AND Duration = " + curTP.Duration.ToString() + ";";
                    SqlCommand updateTP = new SqlCommand(commandText, DBConnection);
                    updateTP.ExecuteNonQuery();

                }
                catch (Exception e2)
                {
                    Console.WriteLine("Exception: " + e2.Message);
                }
            }
        }

        /*
         * This method finds the relevand information of a TherapyPrice curTP 
         * based on the rmtID and therID that are passed as parameters. It then
         * removes the data from the database.
         */ 
        public void removeTherapyPrice(TherapyPrice curTP, String rmtID, String therID)
        {
            String commandText = "DELETE FROM dbo.TherapyPrice " +
                                 "WHERE RMTID = '" + rmtID + "' AND TherapyID = '"
                                 + therID + "' AND Duration = " + 
                                 curTP.Duration.ToString() + ";" ;
            try
            {
                SqlCommand removeTP = new SqlCommand(commandText, DBConnection);
                removeTP.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception in remove: " + e.Message);
            }
        }

        /*
         * This method writes informaiton about Therapy curTher(passed as a parameter) 
         * into the database. If a therapy with the same therapyID exists, it just update
         * the information on that therapy.
         */ 
        public void writeTherapy(Therapy curTher) 
        {
            String commandText = "";
            commandText = "INSERT INTO dbo.Therapy VALUES('" +
                              curTher.TherapyID + "','" + curTher.Name + "'," +
                              curTher.RoomID.ToString() + ");";
            try
            {//try to write
                SqlCommand writeTP = new SqlCommand(commandText, DBConnection);
                writeTP.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                try
                {//try to update if write unsuccessful.
                    commandText = "UPDATE dbo.Therapy SET RoomID = " +
                                    curTher.RoomID.ToString() + " WHERE ID = '" +
                                    curTher.TherapyID + "';";
                    SqlCommand updateTher = new SqlCommand(commandText, DBConnection);
                    updateTher.ExecuteNonQuery();
                }
                catch (Exception e2)
                {
                    Console.WriteLine("Exception: " + e2.Message);
                }
            }
        }

        /*
         * This method queries information about a Therapy based on the therID
         * ,passed as a parameter, and the makes an object of type Therapy and 
         * returns it on success.
         */ 
        public Therapy readTherapy(String therID)
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            Therapy therapy = null;
            try
            {
                commandText = "SELECT Name, RoomID " +
                            "FROM Therapy " +
                            "WHERE ID = '" + therID + "';";
                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();
                if (readResults.Read())
                {
                    therapy = new Therapy(readResults["Name"].ToString(),
                                    int.Parse(readResults["RoomID"].ToString()));
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excep in read:" + e.ToString());
                return null;
            }

            finally
            {
                if (readResults != null)
                    readResults.Close();
            }
            return therapy;

        }

        /*
         * This method finds information on a therapy based on the therID that is
         * passed as a parameter on Therapy table in the database and then removes 
         * that therapy.
         */ 
        public void removeTherapy(String therID)
        {
            String commandText = "DELETE FROM dbo.Therapy " +
                                 "WHERE ID = '"
                                 + therID + "';";
            try
            {
                SqlCommand removeTher = new SqlCommand(commandText, DBConnection);
                removeTher.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception in remove: " + e.Message);
            }
        }

        /*
         * This method writes complete information about an RMT into the database
         * based on curRMT that is passed as a parameter. There is a separate table
         * in the database that stores these information. 
         * If an rmt with the same ID already exists in the database, it just updates
         * the spot with the new information curRMT might hold.
         */ 
        public void writeRMT(RMT curRMT)
        {
            String commandText = "";
            String therapyList = "";
            String timeList = "";

            foreach (Therapy curTher in curRMT.TherapyList)
            {
                /*making a string out of the therapyIDs to store them in the 
                relevant culumn of the database.*/
                therapyList += curTher.TherapyID + ",";
            }
            foreach (TimeSlot curTS in curRMT.TimeList)
            {
                /*making a string out of the timeSlots to store them in the 
                relevant culumn of the database.*/
                timeList += curTS.StartTime.ToShortDateString() + " " + 
                            curTS.StartTime.ToShortTimeString() + "-" + 
                            curTS.EndTime.ToShortDateString() + " " + 
                            curTS.EndTime.ToShortTimeString() + ",";
            }

            commandText = "INSERT INTO dbo.RMT VALUES('" +
                              curRMT.ID + "','" + curRMT.Name + "'," +
                             ( (curRMT.IsAdmin) ? "1" : "0" )+ 
                              ",'" + therapyList + "'" + 
                              ",'" + timeList + "');";
            try
            {
                //try to write the info on curRMT
                SqlCommand writeRMT = new SqlCommand(commandText, DBConnection);
                writeRMT.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                try
                {
                    //try to update info on curRMT if write unsuccessful.
                    commandText = 
                        "UPDATE dbo.RMT SET Name = '" + curRMT.Name +
                        "', Role = " + ((curRMT.IsAdmin) ? "1" : "0" ) + 
                        ", TherapyList = '" + therapyList +
                        "', timeList = '" + timeList +
                        "' WHERE ID = '" + curRMT.ID + "';";
                    SqlCommand updateRMT = new SqlCommand(commandText, DBConnection);
                    updateRMT.ExecuteNonQuery();

                }
                catch (Exception e2)
                {
                    Console.WriteLine("Exception: " + e2.Message);
                }
            }
        }
        
        /*
         * finds and removes information on an RMT in RMT table of the database
         * based on rmtID passed as a parameter.
         */ 
        
        public void removeRMT(String rmtID)
        {
            String commandText = "DELETE FROM dbo.RMT " +
                                 "WHERE ID = '"
                                 + rmtID + "';";
            try
            {
                SqlCommand removeRMT = new SqlCommand(commandText, DBConnection);
                removeRMT.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception in remove: " + e.Message);
            }
        }

        /*
         * This method queries information about an RMT from RMT table of the database,
         * then makes an object of type RMT based on those info. and returns that 
         * object on success.
         */ 
        public RMT readRMT(String rmtID)
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            RMT rmt = null;

            try
            {
                commandText = "SELECT * " +
                            "FROM RMT " +
                            "WHERE ID = '" + rmtID + "';";
                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();

                if (readResults.Read())
                {
                    rmt = new RMT(readResults["ID"].ToString(), readResults["Name"].ToString(),
                     
                        (int.Parse(readResults["Role"].ToString()) == 1));
                    /*
                     * Parsing the string that holds therapyIDs and sotre them in a
                     * LinkedList. This LinkedList will be used to query information
                     * on each of the therapies.
                     */
                    
                    String therapyID = "";
                    LinkedList<String> therapyIDList = new LinkedList<string>();
                    String therapyList = readResults["TherapyList"].ToString().Trim();
                    int end = therapyList.IndexOf(',');
                    while (end > 0)
                    {
                        therapyID = therapyList.Substring(0, end);
                        therapyIDList.AddLast(therapyID);
                        therapyList = therapyList.Substring(end + 1);
                        end = therapyList.IndexOf(',');
                    }

                    /*
                     * Parsing the string that holds timeSlots and make an object of
                     * type TimeSlot out of each and store them in the TimeList of 
                     * the RMT.
                     */
                    String timeSlotList = readResults["TimeList"].ToString().Trim();
                    String startDateTimeStr = "";
                    String endDateTimeStr = "";
                    int middle = timeSlotList.IndexOf('-');
                    end = timeSlotList.IndexOf(',');
                    while (middle > 0 && end > 0)
                    {
                        startDateTimeStr = timeSlotList.Substring(0, middle);
                        endDateTimeStr = timeSlotList.Substring((middle + 1), (end - (middle + 1)));
                        DateTime starDateTime = DateTime.Parse(startDateTimeStr);
                        DateTime endDateTime = DateTime.Parse(endDateTimeStr);
                        TimeSlot curTS = new TimeSlot(starDateTime, endDateTime);
                        rmt.TimeList.AddLast(curTS);
                        timeSlotList = timeSlotList.Substring(end + 1);
                        middle = timeSlotList.IndexOf('-');
                        end = timeSlotList.IndexOf(',');
                    }

                    if (readResults != null)
                        readResults.Close();

                    foreach (String curTherID in therapyIDList) 
                    {
                        Therapy curTher = readTherapy(curTherID);
                        rmt.TherapyList.AddLast(curTher);
                    }
                    foreach (Therapy curTher in rmt.TherapyList) 
                    {
                        LinkedList<TherapyPrice> curTPList = readTherapyPriceList(rmtID, curTher.TherapyID);
                        curTher.PriceList = curTPList;
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excepion in read:" + e.ToString());
                return null;
            }

            finally
            {
                if (readResults != null)
                    readResults.Close();
            }

            return rmt;
        }

        /*
         * This method is used to read information on a room based on the roomName
         * passed as a parameter. It then returns an object of type Room on success.
         */ 
        public Room readRoom(String roomName)
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            Room room = null;

            try
            {
                commandText = "SELECT * " +
                            "FROM Room " +
                            "WHERE Name = '" + roomName + "';";
                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();

                if (readResults.Read())
                {
                    room = new Room(roomName);
                    room.ScheduleID = int.Parse(readResults["ScheduleID"].ToString());
                    
                    if (readResults != null)
                        readResults.Close();

                    room.WeekSchedule = readSchedule(room.ScheduleID);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excepion in read:" + e.ToString());
                return null;
            }
            finally
            {
                if (readResults != null)
                    readResults.Close();
            }

            return room;
        }

        /*
         * This method is used to write information about a room on the relevant 
         * spot of the Room table in database.
         */ 
        public void writeRoom(Room room)
        {
            String commandText = "";
            try
            {
                commandText = "INSERT INTO Room VALUES('" +
                                room.Name + "'," + room.ScheduleID + " );";

                SqlCommand writeCust = new SqlCommand(commandText, DBConnection);
                writeCust.ExecuteNonQuery();
                writeSchedule(room.ScheduleID, room.WeekSchedule, -1);
            }
            catch (Exception e)
            {
                //Console.WriteLine("Exception: " + e.Message);
            }
        }
    
        /*
         * This method queries the database on information about a schedule for a 
         * week(7 daily shcedules.) This schedule is found based on the scheduleID
         * that is passed as a parameter to this funstion.
         */ 
        public DailySched[] readSchedule(int scheduleID)
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            DailySched[] week = new DailySched[7];
            String[] schedSlotList = new String[7];

            try
            {
                commandText = "SELECT * " +
                            "FROM WeekSchedule " +
                            "WHERE ID = '" + scheduleID + "';";
                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();

                if (readResults.Read())
                {
                    /* 
                     * storing information about each schedule spot 
                     * on a LinkedList of strings for later parse
                     */
                    for (int i = 0; i < schedSlotList.Length; i++)
                    {
                        schedSlotList[i] = readResults[i + 1].ToString();
                    }
                }

                if (readResults != null)
                    readResults.Close();

                for (int i = 0; i < schedSlotList.Length; i++)
                {
                    /*
                     * parsing the information on each schedSlot.
                     */ 
                    int endIndex = -1;
                    int j = 0;
                    week[i] = new DailySched(Room.getDayForIndex(i));
                    week[i].DaySchedule = new SchedSlot[24];
                    while ((endIndex = schedSlotList[i].IndexOf(",")) != -1 && j < week[i].DaySchedule.Length)
                    {
                        int schedSlotID = int.Parse(schedSlotList[i].Substring(0, endIndex));

                        if (schedSlotID == -1)
                        {
                            // it's a free schedule slot 
                            week[i].DaySchedule[j] = new SchedSlot();
                        }
                        else
                        {
                            week[i].DaySchedule[j] = readSchedSlot(schedSlotID);
                        }

                        schedSlotList[i] = schedSlotList[i].Substring(endIndex + 1);
                        j++;
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excepion in read:" + e.ToString());
                return null;
            }
            finally
            {
                if (readResults != null)
                    readResults.Close();
            }

            return week;
        }
        /*
         * This method is used to write information about a dailySched for 7
         * days on database.
         * index chooses a day in a week, if it's -1 
         * updates the whole week schedule 
         */
        public int writeSchedule(int scheduleID, DailySched[] week, int index)
        {
            String commandText = "";
            /* schedule slot is not in DB
             * generate a unique ID
             */
            String[] schedSlotList = new String[7];

            for (int i = 0; i < week.Length; i++)
            {
                schedSlotList[i] = "";

                for (int j = 0; j < week[i].DaySchedule.Length; j++)
                {
                    SchedSlot curSC = week[i].DaySchedule[j];

                    if (curSC.State == 0 && curSC.RMT == "")
                    {
                        /* free */
                        schedSlotList[i] += "-1,";
                    }
                    else
                    {
                        schedSlotList[i] += curSC.ID + ",";
                    }
                }
            }

            if (scheduleID == -1)
            {
                commandText = "SELECT MAX(ID) As MaxID FROM WeekSchedule";
                SqlDataReader readResults = null;
                SqlCommand command;

                try
                {
                    command = new SqlCommand(commandText, DBConnection);
                    readResults = command.ExecuteReader();
                    if (readResults.Read())
                    {
                        String temp = readResults["MaxID"].ToString();
                        scheduleID = int.Parse((temp == "") ? "0" : temp) + 1;
                    }

                    commandText = "INSERT INTO WeekSchedule VALUES(" +
                                scheduleID.ToString() + ",'" +
                                schedSlotList[0] + "','" +
                                schedSlotList[1] + "','" +
                                schedSlotList[2] + "','" +
                                schedSlotList[3] + "','" +
                                schedSlotList[4] + "','" +
                                schedSlotList[5] + "','" +
                                schedSlotList[6] + "');";
                }
                catch (Exception e)
                {
                    Console.WriteLine("Exception: " + e.Message);
                }
                finally
                {
                    if (readResults != null)
                        readResults.Close();
                }

            }
            else
            {
                if (index == -1)
                {
                    commandText = "UPDATE WeekSchedule SET " +
                                  Room.getDayForIndex(0) + " = '" + schedSlotList[0] + "'," +
                                  Room.getDayForIndex(1) + " = '" + schedSlotList[1] + "'," +
                                  Room.getDayForIndex(2) + " = '" + schedSlotList[2] + "'," +
                                  Room.getDayForIndex(3) + " = '" + schedSlotList[3] + "'," +
                                  Room.getDayForIndex(4) + " = '" + schedSlotList[4] + "'," +
                                  Room.getDayForIndex(5) + " = '" + schedSlotList[5] + "'," +
                                  Room.getDayForIndex(6) + " = '" + schedSlotList[6] + "'" +
                                  " WHERE ID = " + scheduleID.ToString() + ";";
                }
                else
                {
                    commandText = "UPDATE WeekSchedule SET " +
                                 Room.getDayForIndex(index) + " = '" + schedSlotList[index] + "'" +
                               " WHERE ID = " + scheduleID.ToString() + ";";
                }
            }

            try
            {
                SqlCommand writeApp = new SqlCommand(commandText, DBConnection);
                writeApp.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                Console.WriteLine("Exception: " + e.Message);
            }
            return scheduleID;
        }

        /*
         * This method reads a schedSlot based on schedSlotID passed as a parameter 
         * and makes an object of type SchedSlot out of these info. returns the
         * object on success.
         */ 
        
        public SchedSlot readSchedSlot(int schedSlotID)
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            SchedSlot schedSlot = null;

            try
            {
                commandText = "SELECT * " +
                            "FROM ScheduleSlot " +
                            "WHERE ID = '" + schedSlotID + "';";
                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();

                if (readResults.Read())
                {
                    schedSlot = new SchedSlot(schedSlotID, int.Parse(readResults["State"].ToString()),
                                              readResults["RMTName"].ToString(), readResults["TherapyName"].ToString(),
                                              readResults["CustomerName"].ToString());
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excepion in read:" + e.ToString());
                return null;
            }
            finally
            {
                if (readResults != null)
                    readResults.Close();
            }

            return schedSlot;
        }

        /*
         * This method writes information about a scheduleSlot based on the info
         * held by schedSlot(passed as a parameter). If the relevant spot in the
         * ScheudleSlot table is already written then it tries to update the spot
         * with the new information schedSlot may hold.
         */ 
        public int writeSchedSlot(SchedSlot schedSlot)
        {
            String commandText = "";
            int schedSlotID = schedSlot.ID;
            /* schedule slot is not in DB
             * generate a unique ID
             */
            if (schedSlotID == -1)
            {
                commandText = "SELECT MAX(ID) As MaxID FROM dbo.ScheduleSlot";
                SqlDataReader readResults = null;
                SqlCommand command;

                try
                {
                    command = new SqlCommand(commandText, DBConnection);
                    readResults = command.ExecuteReader();
                    if (readResults.Read())
                    {
                        String temp = readResults["MaxID"].ToString();
                        schedSlotID = int.Parse((temp == "") ? "0" : temp) + 1;
                    }

                    commandText = "INSERT INTO dbo.ScheduleSlot VALUES(" +
                                schedSlotID.ToString() + "," + schedSlot.State.ToString() + ",'" +
                                schedSlot.RMT + "','" + schedSlot.Therapy + "','" +
                                schedSlot.Customer + "');";
                }
                catch (Exception e)
                {
                    Console.WriteLine("Exception: " + e.Message);
                }
                finally
                {
                    if (readResults != null)
                        readResults.Close();
                }

            }
            else
            {
                commandText = "UPDATE dbo.ScheduleSlot SET State = " +
                              schedSlot.State.ToString() + ", RMTName = '" +
                              schedSlot.RMT + "', TherapyName = '" + schedSlot.Therapy + "', CustomerName = '" +
                              schedSlot.Customer +
                              "' WHERE ID = " + schedSlotID + ";";
            }

            try
            {
                SqlCommand writeApp = new SqlCommand(commandText, DBConnection);
                writeApp.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                Console.WriteLine("Exception: " + e.Message);
            }

            return schedSlotID;
        }

        /* removes the entry with the given id in the scheduleslot table */
        public void removeSchedSlot(int schedSlotID)
        {
            String commandText = "DELETE FROM ScheduleSlot" +
                                 " WHERE ID = "
                                 + schedSlotID + ";";
            try
            {
                SqlCommand removeApp = new SqlCommand(commandText, DBConnection);
                removeApp.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception in remove: " + e.Message);
            }
        }

        /*
         * This method write information on the Appointment curApp 
         * on the Appointment table in the database. If already 
         */ 
        public int writeAppointment(Appointment curApp)
        {
            String commandText = "";
            int appointmentID = curApp.ID;

            if (appointmentID == -1)
            {
                commandText = "SELECT MAX(ID) As MaxID FROM Appointment";
                SqlDataReader readResults = null;
                SqlCommand command;

                try
                {
                    command = new SqlCommand(commandText, DBConnection);
                    readResults = command.ExecuteReader();
                    if (readResults.Read())
                    {
                        String temp = readResults["MaxID"].ToString();
                        appointmentID = int.Parse((temp == "") ? "0" : temp) + 1;
                    }

                    commandText = "INSERT INTO Appointment VALUES(" +
                                     appointmentID + ",'" + curApp.RMTName + "','" +
                                    curApp.TherapyName + "','" + curApp.Room + "','" +
                                    curApp.Time.StartTime.ToShortDateString() + " " +
                                    curApp.Time.StartTime.ToShortTimeString() + "','" +
                                    curApp.Time.EndTime.ToShortDateString() + " " +
                                    curApp.Time.EndTime.ToShortTimeString() + "', " +
                                    curApp.Price.ToString() + " );";
                }
                catch (Exception e)
                {
                    Console.WriteLine("Exception: " + e.Message);
                }
                finally
                {
                    if (readResults != null)
                        readResults.Close();
                }

            }
            else
            {
                commandText = "UPDATE Appointment SET RMTName = " +
                              curApp.RMTName + ", TherapyName = '" +
                              curApp.TherapyName + "', RoomName = '" + curApp.Room + "', Start = '" +
                              curApp.Time.StartTime.ToShortDateString() + " " +
                              curApp.Time.StartTime.ToShortTimeString() + "', Finish = '" +
                              curApp.Time.EndTime.ToShortDateString() + " " +
                              curApp.Time.EndTime.ToShortTimeString() + "'" +
                              " WHERE ID = " + appointmentID.ToString() + ";";
            }

            try
            {
                SqlCommand writeApp = new SqlCommand(commandText, DBConnection);
                writeApp.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                Console.WriteLine("Exception: " + e.Message);
            }

            return appointmentID;
        }
        
        /*
         * This method finds the relevant appointment base on appID passed as parameter
         * in the Appointment table of the database and the removes the information.
         */ 
        
        public void removeAppointment(int appID)
        {
            String commandText = "DELETE FROM Appointment" +
                                 " WHERE ID = "
                                 + appID.ToString() + ";";
            try
            {
                SqlCommand removeApp = new SqlCommand(commandText, DBConnection);
                removeApp.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception in remove: " + e.Message);
            }
        }

        /*
         * This method is used to query the database for information about
         * an Appointment based on appID. It makes an Object of type 
         * Appointment on success and returns it.
         */ 
        public Appointment readAppointment(int appID)
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            Appointment appointment = null;
            try
            {
                commandText = "SELECT * " +
                              "FROM Appointment " +
                              "WHERE ID = " + appID + ";";

                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();
                if (readResults.Read())
                {
                    TimeSlot time = new TimeSlot(DateTime.Parse(readResults["Start"].ToString()),
                                                  DateTime.Parse(readResults["Finish"].ToString()));
                    appointment = new Appointment(readResults["RMTName"].ToString(),
                                                   readResults["TherapyName"].ToString(),
                                                   readResults["RoomName"].ToString(), time,
                                                   int.Parse(readResults["Price"].ToString()));
                    appointment.ID = appID;
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excep in read:" + e.ToString());
                return null;
            }
            finally
            {
                if (readResults != null)
                    readResults.Close();
            }
            return appointment;

        }

        /*
         * write information about a Customer curCust(passed as parameter) on the
         * Customer table of the database. Update the info if already written.
         */
        public void writeCustomer(Customer curCust)
        {
            String commandText = "";
            String appList = " ";
            try
            {
                foreach (Appointment curApp in curCust.AppointmentList)
                {
                    appList += curApp.ID.ToString() + ",";
                }
                commandText = "INSERT INTO dbo.Customer VALUES('" +
                                curCust.ID + "','" + curCust.Name + "','" +
                                appList + "'," + "0" + " );";
                SqlCommand writeCust = new SqlCommand(commandText, DBConnection);
                writeCust.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                try
                {

                    commandText = "UPDATE Customer SET Balance = " +
                                    curCust.Balance.ToString() + ", AppointmentList = '" +
                                    appList + "', Name = '" + curCust.Name + "' " +
                                     "WHERE ID = '" + curCust.ID + "';";
                    SqlCommand updateTP = new SqlCommand(commandText, DBConnection);
                    updateTP.ExecuteNonQuery();

                }
                catch (Exception e2)
                {
                    Console.WriteLine("Exception: " + e2.Message);
                }
            }

        }

        /*
         * This method finds information about a cusomer on Customer table of 
         * the database based on custID and then removes it.
         */
        public void removeCustomer(String custID)
        {
            String commandText = "DELETE FROM Customer " +
                                 "WHERE ID = '"
                                 + custID + "';";
            try
            {
                SqlCommand removeCust = new SqlCommand(commandText, DBConnection);
                removeCust.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine("Exception in remove: " + e.Message);
            }
        }

        /*
         * this method queries the database about information on a Customer based on 
         * the custID that is passed as a parameter. It then makes an object of type 
         * Customer and returns it on success.
         */ 
        public Customer readCustomer(String custID)
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            Customer cust = null;

            try
            {
                commandText = "SELECT * " +
                            "FROM Customer " +
                            "WHERE ID = '" + custID + "';";
                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();
                if (readResults.Read())
                {
                    int endIndex = -1;
                    String appList = readResults["AppointmentList"].ToString().Trim();

                    cust = new Customer(readResults["Name"].ToString(),
                                    readResults["ID"].ToString(),
                                    int.Parse(readResults["Balance"].ToString()));

                    if (readResults != null)
                        readResults.Close();

                    while ((endIndex = appList.IndexOf(",")) != -1)
                    {
                        int appointmentID = int.Parse(appList.Substring(0, endIndex));

                        if (appointmentID != -1)
                        {
                            // add the appointment
                            cust.AppointmentList.AddLast(readAppointment(appointmentID));
                        }

                        appList = appList.Substring(endIndex + 1);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excepion in read:" + e.ToString());
                return null;
            }
            finally
            {
                if (readResults != null)
                    readResults.Close();
            }

            return cust;
        }

        /*
         * This method queries information each of the users based on the userID
         * passed as a parameter. It then makes and object of type User and returns 
         * is on success.
         */ 
        public User readUser(String userID)
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            User user = null;

            try
            {
                commandText = "SELECT * " +
                            "FROM Users " +
                            "WHERE UserID = '" + userID + "';";
                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();
                if (readResults.Read())
                {
                    user = new User(readResults["UserID"].ToString(),
                                    readResults["Password"].ToString(),
                                    int.Parse(readResults["Type"].ToString()));
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excepion in read:" + e.ToString());
                return null;
            }
            finally
            {
                if (readResults != null)
                    readResults.Close();
            }

            return user;
        }

        /*
         * This method writes information about a User on the User table of the 
         * database. If these information are already in there then it just updates
         * those infro based on the userID, password and type that are passed as 
         * parameters.
         */ 
        public void writeUser(String userID, String password, int type)
        {
            String commandText = "";
            try
            {
                commandText = "INSERT INTO Users VALUES('" +
                                userID + "','" + password + "'," +
                                type + " );";
                SqlCommand writeCust = new SqlCommand(commandText, DBConnection);
                writeCust.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                try
                {

                    commandText = "UPDATE Users SET Password = '" +
                                    password + "', Type = " + type + " " +
                                     "WHERE UserID = '" + userID + "';";
                    SqlCommand updateTP = new SqlCommand(commandText, DBConnection);
                    updateTP.ExecuteNonQuery();

                }
                catch (Exception e2)
                {
                    Console.WriteLine("Exception: " + e2.Message);
                }
            }
        }

        /*
         * This method gets a specific user type and finds it in the databse. It 
         * then makes an object of type User out of each of them and places them 
         * in a LinkedList and returns that LinkedList.
         */ 
        public LinkedList<User> readAllUsers(int type)
        {
            String commandText = "";
           SqlDataReader readResults = null;
           SqlCommand command;
           User user = null;
           LinkedList<User> userList = new LinkedList<User>();
           try
           {
               commandText = "SELECT * " +
                           "FROM Users " +
                           "WHERE Type = " + type + ";";
               command = new SqlCommand(commandText, DBConnection);
               readResults = command.ExecuteReader();
               while (readResults.Read())
               {
                   user = new User(readResults["UserID"].ToString(),
                                   "",
                                   int.Parse(readResults["Type"].ToString()));
                   userList.AddLast(user);
               }
           }
           catch (Exception e)
           {
               Console.WriteLine("Excepion in read:" + e.ToString());
               return null;
           }
            finally
            {
                if (readResults != null)
                    readResults.Close();
            }

            return userList;
        }

        /*
         * This method reads all the Names of the RMTs available in the RMT 
         * table and places them into a String LinkedList.
         */ 
        public LinkedList<String> readAllRMTs_Name()
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            LinkedList<String> rmtList = new LinkedList<String>();
            try
            {
                commandText = 
                    "SELECT * " +
                    "FROM RMT ;";
                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();
                while (readResults.Read())
                {
                    String name = readResults["Name"].ToString();
                    rmtList.AddLast(name);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excepion in read:" + e.ToString());
                return null;
            }
            finally
            {
                if (readResults != null)
                    readResults.Close();
            }

            return rmtList;
        }
        /*
        * This method reads all the RMTs available in the RMT 
        * table and places them into a RMT LinkedList.
        */
        public LinkedList<RMT> readAllRMTs()
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            LinkedList<RMT> rmtList = new LinkedList<RMT>();
            try
            {
                commandText =
                    "SELECT * " +
                    "FROM RMT ;";
                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();
                while (readResults.Read())
                {
                    RMT rmt = new RMT(readResults["ID"].ToString(), 
                                      readResults["Name"].ToString(),
                                      (int.Parse(readResults["Role"].ToString()) == 1));
                    rmtList.AddLast(rmt);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excepion in read:" + e.ToString());
                return null;
            }
            finally
            {
                if (readResults != null)
                    readResults.Close();
            }

            return rmtList;
        }

        /*
        * This method reads all the IDs of the RMTs available in the RMT 
        * table and places them into a String LinkedList.
        */
        public LinkedList<String> readAllRMTs_ID()
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            LinkedList<String> rmtList = new LinkedList<String>();
            try
            {
                commandText =
                    "SELECT * " +
                    "FROM RMT ;";
                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();
                while (readResults.Read())
                {
                    String name = readResults["ID"].ToString();
                    rmtList.AddLast(name);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excepion in read:" + e.ToString());
                return null;
            }
            finally
            {
                if (readResults != null)
                    readResults.Close();
            }

            return rmtList;
        }

        /*
         * This method reads all the types of the therapies available in the therapy
         * table and places them into a String LinkedList.
         */
        public LinkedList<Therapy> readAllTherapies()
        {
            String commandText = "";
            SqlDataReader readResults = null;
            SqlCommand command;
            LinkedList<Therapy> therapy_List = new LinkedList<Therapy>();
            try
            {
                commandText =
                    "SELECT * " +
                    "FROM Therapy ;";
                command = new SqlCommand(commandText, DBConnection);
                readResults = command.ExecuteReader();
                while (readResults.Read())
                {
                    Therapy therapy = new Therapy(readResults["Name"].ToString());
                    therapy_List.AddLast(therapy);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Excepion in read:" + e.ToString());
                return null;
            }
            finally
            {
                if (readResults != null)
                    readResults.Close();
            }

            return therapy_List;
        }
    }
}

